<?php

App::uses('Crud', 'Model');
App::uses('User', 'Model');
App::uses('Workstation', 'Model');
App::uses('WorkstationConfig', 'Model');
App::uses('Certification', 'Model');
App::uses('TeamUser', 'Model');
App::uses('TeamLine', 'Model');
App::uses('Line', 'Model');
App::uses('UserArea', 'Model');

class Team extends Crud
{

    /**
     * Al Agregar/eliminar/actualizar las constantes de los modelos implica ir
     * y actualizar los comentarios en los campos de la tabla asociada a este modelo.
     */
    const STATUS_ENABLED = 1;
    const STATUS_DISABLED = 0;

    /**
     * Obtenemos los equipos que estan vigentes
     * @param string $date Y-m-d
     * @return array
     */
    public function getEnabled($strDate = null)
    {
        $date = new DateTime($strDate);
        $ymd = $date->format('Y-m-d');
        $filters = array(
            'conditions' => array(
                'status' => self::STATUS_ENABLED,
                'start_date >=' => $ymd,
                'end_date <=' => $ymd,
            ),
        );
        $records = $this->find('all', $filters);
        return $this->flatArray($records);
    }

    /**
     * @param $areaId
     * @return array
     */
    public function getEnabledByArea($areaId, $teamId = null)
    {
        $filters = array(
            'fields' => array(
                'Team.*',
                'virtualLine.colour as colour_virtual_line',
                'realLine.colour as colour_real_line',
                'realLine.name as real_line',
                's.name as shift',
                'u.nomina as nomina',
                'u.full_name as leader',
            ),
            'joins' => array(
                array(
                    'table' => 'lines',
                    'alias' => 'realLine',
                    'type' => 'left',
                    'conditions' => array(
                        'Team.real_line_id = realLine.id'
                    ),
                ),
                array(
                    'table' => 'lines',
                    'alias' => 'virtualLine',
                    'type' => 'left',
                    'conditions' => array(
                        'Team.line_id = virtualLine.id'
                    ),
                ),
                array(
                    'table' => 'us3rs_r4gg14n4',
                    'alias' => 'u',
                    'type' => 'left',
                    'conditions' => array(
                        'Team.leader_id = u.id'
                    ),
                ),
                array(
                    'table' => 'shifts',
                    'alias' => 's',
                    'type' => 'inner',
                    'conditions' => array(
                        'Team.shift_id = s.id'
                    ),
                )
            ),
            'conditions' => array(
                'Team.status' => self::STATUS_ENABLED,
            ),
            'order' => array(
                'realLine.name' => 'ASC',
                'Team.name' => 'ASC',
            ),
        );
        if ($areaId !== null) {
            $filters['conditions']['Team.area_id'] = $areaId;
        }
        if ($teamId !== null) {
            $filters['conditions']['Team.id'] = $teamId;
        }
        $records = $this->find('all', $filters);
        return $this->flatArray($records);
    }

    public function getMhForTeam($teamId, $lineId)
    {
        $filters = array(
            'fields' => array(
                'u.id as ui',
                'u.full_name as un',
                'u.nomina as unm',
                'ul.level as l',
                'w.id as workstationId',
                'w.name as workstationName',
                'w.notification_end as workstationNotificationDays',
                'c.id as certificationId',
                'c.end as certificationEnd',
                'c.life as certificationLife',
                'c.success as certificationSuccess',
                'c.status as certificationStatus',
                'c.migrated as certificationMigrated',
                'ul.certifications as nc',
                'c.training as certificationTraining',
                'IF(ut.leader = 1, \'leader\', \'\') as leader'
            ),
            'joins' => array(
                array(
                    'table' => 'team_users',
                    'alias' => 'ut',
                    'type' => 'INNER',
                    'conditions' => array(
                        'ut.team_id = Team.id',
                        'ut.status' => TeamUser::STATUS_ENABLED,
                    ),
                ),
                array(
                    'table' => 'user_lines',
                    'alias' => 'ul',
                    'type' => 'INNER',
                    'conditions' => array(
                        'ut.user_id = ul.user_id',
                        'ul.line_id' => $lineId,
                    ),
                ),
                array(
                    'table' => 'us3rs_r4gg14n4',
                    'alias' => 'u',
                    'type' => 'INNER',
                    'conditions' => array(
                        'ut.user_id = u.id',
                    ),
                ),
                array(
                    'table' => 'workstations',
                    'alias' => 'w',
                    'type' => 'INNER',
                    'conditions' => array(
                        'w.line_id' => $lineId,
                        'w.status' => Workstation::STATUS_ENABLED,
                    ),
                ),
                array(
                    'table' => 'certifications',
                    'alias' => 'c',
                    'type' => 'LEFT',
                    'conditions' => array(
                        'c.workstation_id = w.id',
                        'c.user_id = u.id',
                        'c.status' => array(
                            Certification::STATUS_ENABLED,
                            Certification::STATUS_PROPAGATION,
                        ),
                    ),
                ),
            ),
            'conditions' => array(
                'Team.id' => $teamId,
            ),
        );
        return $this->flatArray($this->find('all', $filters));
    }

    public function getBySeed($areaId, $seed)
    {
        $filters = array(
            'fields' => array(
                'Team.id',
                'Team.area_id',
                'Team.name',
                'Team.line',
                'Team.colour_line',
                'Team.colour_real',
                'Team.shift',
                'Team.line_id',
                'Team.type',
            ),
            'joins' => array(
                array(
                    'table' => 'team_users',
                    'alias' => 'tu',
                    'type' => 'LEFT',
                    'conditions' => array(
                        'tu.team_id = Team.id',
                        'tu.status' => TeamUser::STATUS_ENABLED,
                    ),
                ),
                array(
                    'table' => 'team_lines',
                    'alias' => 'tl',
                    'type' => 'LEFT',
                    'conditions' => array(
                        'tl.team_id = Team.id',
                        'tl.status' => TeamLine::STATUS_ENABLED,
                    ),
                ),
                array(
                    'table' => 'lines',
                    'alias' => 'l',
                    'type' => 'LEFT',
                    'conditions' => array(
                        'l.id = tl.line_id',
                        'l.status' => Line::STATUS_ENABLED,
                    ),
                ),
                array(
                    'table' => 'us3rs_r4gg14n4',
                    'alias' => 'u',
                    'type' => 'LEFT',
                    'conditions' => array(
                        'u.id = tu.user_id',
                        'NOT' => array('u.status' => User::STATUS_DISABLED)
                    ),
                ),
            ),
            'conditions' => array(
                'Team.status' => self::STATUS_ENABLED,
                'Team.area_id' => $areaId,
                'OR' => array(
                    'u.nomina LIKE' => "%$seed%",
                    'u.full_name LIKE' => "%$seed%",
                    'u.curp LIKE' => "%$seed%",
                    'Team.name LIKE' => "%$seed%",
                    'l.name LIKE' => "%$seed%",
                )
            ),
            'order' => array(
                'line' => 'ASC',
                'name' => 'ASC',
            ),
        );
        return $this->flatArray($this->find('all', $filters));
    }

    public function getTeamsByUserArea($userId)
    {
        $filters = array(
            'fields' => array(
                'Team.id as value',
                'Team.name as text'
            ),
            'joins' => array(
                array(
                    'table' => 'user_areas',
                    'alias' => 'ua',
                    'type' => 'INNER',
                    'conditions' => array(
                        'ua.user_id' => $userId,
                        'ua.status' => UserArea::STATUS_ENABLED
                    ),
                ),
            ),
            'conditions' => array(
                'Team.area_id = ua.area_id',
                'Team.status' => self::STATUS_ENABLED
            ),
        );
        return $this->flatArray($this->find('all', $filters));
    }

    public function magicTeams()
    {
        $db = $this->getDataSource();
        $db->fetchAll('UPDATE teams set status = 0 where type = 2');
        $db->fetchAll("INSERT INTO teams(area_id, colour_line, id, line, name, shift, status, type)
SELECT l.area_id, '#e5b604', uuid(), l.name, CONCAT(l.name,' - ', s.name), s.name, 1, 2
FROM shift_models sm
INNER JOIN `lines` l ON l.id = sm.line_id
INNER JOIN `shifts` s ON s.id = sm.shift_id
WHERE sm.work_date = DATE(now())
GROUP BY l.area_id, line_id, shift_id");
        $db->fetchAll("INSERT INTO team_users (id, team_id, user_id)
SELECT uuid(), t.id, sm.user_id
FROM shift_models sm
INNER JOIN `lines` l ON l.id = sm.line_id
INNER JOIN `shifts` s ON s.id = sm.shift_id
INNER JOIN teams t ON t.status = 1 AND t.type = 2 AND t.line = l.name AND t.shift = s.name AND t.area_id = l.area_id
WHERE sm.work_date = DATE(now())");
    }

    public function getTotalUsersByLevel($teamId)
    {
        $filters = array(
            'fields' => array(
                'c.name as category',
                'c.colour as color',
                'c.id as levelId',
                'count(tu.id) as value',
            ),
            'joins' => array(
                array(
                    'table' => 'team_users',
                    'alias' => 'tu',
                    'type' => 'inner',
                    'conditions' => array(
                        'tu.team_id = Team.id',
                        'tu.status' => TeamUser::STATUS_ENABLED,
                    ),
                ),
                array(
                    'table' => 'user_lines',
                    'alias' => 'ul',
                    'type' => 'inner',
                    'conditions' => array(
                        'ul.user_id = tu.user_id',
                        'ul.line_id = Team.line_id',
                        'ul.status' => UserLine::STATUS_ENABLED,
                    ),
                ),
                array(
                    'table' => 'categorizations',
                    'alias' => 'c',
                    'type' => 'inner',
                    'conditions' => array(
                        'c.id = ul.level',
                        'c.status' => Categorization::STATUS_ENABLED,
                    ),
                ),
            ),
            'conditions' => array(
                'Team.id' => $teamId,
            ),
            'group' => array(
                'c.id',
                'c.name',
                'c.colour',
            ),
            'order' => array(
                'c.orden asc'
            ),
        );
        return $this->flatArray($this->find('all', $filters));
    }

    /**
     * OBtenemos el numero de certificaciones exitosas y entrenamiento para un equipo
     * @param $teamId
     * @return array
     */
    public function getTotalCertifications($teamId)
    {
        $filters = array(
            'fields' => array(
                'count(Team.id) as total',
                'sum(c.success) as success',
                'sum(c.training) as training',
            ),
            'joins' => array(
                array(
                    'table' => 'lines',
                    'alias' => 'l',
                    'type' => 'inner',
                    'conditions' => array(
                        'l.id = Team.line_id',
                    ),
                ),
                array(
                    'table' => 'workstations',
                    'alias' => 'w',
                    'type' => 'inner',
                    'conditions' => array(
                        'w.line_id = l.id',
                        'w.status' => Workstation::STATUS_ENABLED,
                    ),
                ),
                array(
                    'table' => 'team_users',
                    'alias' => 'tu',
                    'type' => 'inner',
                    'conditions' => array(
                        'tu.team_id = Team.id',
                        'tu.status' => TeamUser::STATUS_ENABLED,
                    ),
                ),
                array(
                    'table' => 'certifications',
                    'alias' => 'c',
                    'type' => 'left',
                    'conditions' => array(
                        'c.workstation_id = w.id',
                        'c.user_id = tu.user_id',
                        'c.status' => Certification::STATUS_ENABLED,
                    ),
                ),
            ),
            'conditions' => array(
                'tu.status' => self::STATUS_ENABLED,
                'Team.id' => $teamId,
            ),
        );
        return $this->find('first', $filters);
    }

    /**
     * Obtenemos todos los asociados del equipo que tiene N cierto nivel y con al menos M dias
     * @return array
     */
    public function getByTeamAndDaysAndLevel($teamId, $days, $lowLevels)
    {
        $filters = array(
            'fields' => array(
                'ul.user_id',
            ),
            'joins' => array(
                array(
                    'table' => 'team_users',
                    'alias' => 'tu',
                    'type' => 'inner',
                    'conditions' => array(
                        'tu.team_id = Team.id',
                        'tu.status' => TeamUser::STATUS_ENABLED,
                    ),
                ),
                array(
                    'table' => 'user_lines',
                    'alias' => 'ul',
                    'type' => 'inner',
                    'conditions' => array(
                        'ul.user_id = tu.user_id',
                        'ul.line_id = Team.line_id',
                        'ul.status' => UserLine::STATUS_ENABLED,
                        'ul.days >=' => $days,
                        //'ul.assign_date IS NOT NULL',
                    ),
                ),
            ),
            'conditions' => array(
                'tu.status' => self::STATUS_ENABLED,
                'Team.id' => $teamId,
                'ul.level' => $lowLevels,
            ),
            'group' => array(
                'ul.user_id',
            ),
        );
        return $this->find('count', $filters);
    }

}
